package com.tcs.dao;

import java.util.ArrayList;

import java.util.List;

import javax.annotation.Resource;

import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.tcs.model.Requisition;
import com.tcs.model.RequisitionClose;
import com.tcs.model.RequisitionDetail;
import com.tcs.model.RequisitionState;

import com.tcs.service.RequisitionService;

@Repository("orderDao")
public class RequisitionDaoImpl implements RequisitionDao {

	@Autowired
	private SessionFactory sessionFactory;

	@Resource
	private RequisitionService requisitionService;
	
	@Transactional
	public void saveRequisition(Requisition requisition) {
		sessionFactory.getCurrentSession().saveOrUpdate(requisition);

	}

	@Transactional
	public void saveRequisitionDetail(RequisitionDetail requisitionDetail) {
		sessionFactory.getCurrentSession().saveOrUpdate(requisitionDetail);
	}

	@Transactional
	public void deleteRequisitionDetail(RequisitionDetail requisitionDetail) {
		sessionFactory.getCurrentSession().delete(requisitionDetail);
	}

	@Transactional
	public void deleteRequisitionDetail(int id) {
		List<RequisitionDetail> list = this.listRequisitionDetail();
		for (int i = 0; i < list.size(); i++) {
			if (list.get(i).getArticle().getArticleId() == id) {
				requisitionService.deleteRequisitionDetail(requisitionService
						.getRequisitionDetail(list.get(i)
								.getRequisitionDetailID()));

			}
		}

	}

	@Transactional
	public void saveRequisitionClose(RequisitionClose requisitionClose) {
		sessionFactory.getCurrentSession().save(requisitionClose);
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	public List<Requisition> listRequisitionsClose() {
		List<Requisition> list = (List<Requisition>) sessionFactory
				.getCurrentSession().createCriteria(Requisition.class).list();
		List<Requisition> subList = new ArrayList<Requisition>();
		for (int i = 0; i < list.size(); i++) {
			if ((list.get(i)).getState().getStateId() == 2) {
				subList.add(list.get(i));
			}
		}
		return subList;
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	public List<RequisitionClose> getListRequisitionClose() {
		return sessionFactory.getCurrentSession()
				.createCriteria(RequisitionClose.class).list();

	}
	
	@Transactional(readOnly = true) 
	public Requisition getRequisition(int ID) {
		Requisition requisition = (Requisition) sessionFactory
				.getCurrentSession()
				.createQuery(
						"FROM Requisition R WHERE R.requisitionId = " + ID + "")
				.uniqueResult();
		return requisition;
	}

	@Transactional(readOnly = true) 
	public RequisitionDetail getRequisitionDetail(int ID) {
		RequisitionDetail requisitionDetail = (RequisitionDetail) sessionFactory
				.getCurrentSession()
				.createQuery(
						"FROM RequisitionDetail RD WHERE RD.requisitionDetailID = "
								+ ID + "").uniqueResult();
		return requisitionDetail;
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	public List<RequisitionDetail> listRequisitionDetailByReq(int ID) {
		List<RequisitionDetail> list = (List<RequisitionDetail>) sessionFactory
				.getCurrentSession().createCriteria(RequisitionDetail.class)
				.list();
		List<RequisitionDetail> subList = new ArrayList<RequisitionDetail>();
		for (int i = 0; i < list.size(); i++) {
			if ((list.get(i)).getRequisition().getRequisitionId() == ID) {
				subList.add(list.get(i));
			}
		}
		return subList;
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	private List<RequisitionDetail> listRequisitionDetail() {
		return (List<RequisitionDetail>) sessionFactory.getCurrentSession()
				.createCriteria(RequisitionDetail.class).list();

	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	public List<RequisitionClose> listRequisitionDetailCloseByReq(int ID) {
		List<RequisitionClose> list = (List<RequisitionClose>) sessionFactory
				.getCurrentSession().createCriteria(RequisitionClose.class)
				.list();
		List<RequisitionClose> subList = new ArrayList<RequisitionClose>();
		for (int i = 0; i < list.size(); i++) {
			if ((list.get(i)).getRequisition().getRequisitionId() == ID) {
				subList.add(list.get(i));
			}
		}
		return subList;
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	public List<Requisition> listRequisitionByUser(int ID) {
		List<Requisition> list = (List<Requisition>) sessionFactory
				.getCurrentSession().createCriteria(Requisition.class).list();
		List<Requisition> subList = new ArrayList<Requisition>();
		for (int i = 0; i < list.size(); i++) {
			if ((list.get(i)).getUser().getUserId() == ID) {
				subList.add(list.get(i));
			}
		}
		return subList;
	}

	@Override
	@Transactional(readOnly = true) 
	public RequisitionState getState(int ID) {
		RequisitionState requisitionState = (RequisitionState) sessionFactory
				.getCurrentSession()
				.createQuery(
						"FROM RequisitionState RS WHERE RS.stateId = " + ID
								+ "").uniqueResult();
		return requisitionState;

	}

	@Override
	@Transactional(readOnly = true) 
	public RequisitionState setState(int ID) {
		RequisitionState requisitionState = (RequisitionState) sessionFactory
				.getCurrentSession()
				.createQuery(
						"FROM RequisitionState RS WHERE RS.stateId = " + ID
								+ "").uniqueResult();
		return requisitionState;

	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	public List<Requisition> listRequisitionByUserByState(int ID, int state) {
		List<Requisition> list = (List<Requisition>) sessionFactory
				.getCurrentSession().createCriteria(Requisition.class)
				.addOrder(Order.asc("requisitionDate")).list();
		List<Requisition> subList = new ArrayList<Requisition>();
		for (int i = 0; i < list.size(); i++) {
			if (((list.get(i)).getUser().getUserId() == ID)
					&& (list.get(i)).getState().getStateId() == state) {
				subList.add(list.get(i));
			}
		}
		return subList;
	}

	

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	public List<Requisition> listofRequisitionbyDate(long sinceDate, long toDate) {

		List<Requisition> list = (List<Requisition>) sessionFactory
				.getCurrentSession()
				.createCriteria(Requisition.class)
				.add(Restrictions
						.sqlRestriction("state_requisition_id = 1 AND UNIX_TIMESTAMP(requisition_date) between "
								+ sinceDate + " and " + toDate)).list();

		return list;
	}

	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true) 
	public List<Object[]> listRequisitionOpen() {

		SQLQuery query = sessionFactory
				.getCurrentSession()
				.createSQLQuery(
						"SELECT u.user_id, u.user_firstname, u.user_lastname,( SELECT COUNT(*) FROM requisition r WHERE u.user_id = r.user_user_id AND r.state_requisition_id = 1) As ReqOpen,( SELECT COUNT(*) FROM requisition r WHERE u.user_id = r.user_user_id AND r.state_requisition_id = 2) As ReqClose FROM user u GROUP BY u.user_id, u.user_firstname, u.user_lastname HAVING ReqOpen > ReqClose Order by ReqOpen desc");

		List<Object[]> listReult = query.list();

		for (int i = 0; i < listReult.size(); i++) {
			System.out.println("Nombre " + i + ": " + listReult.get(i)[0]
					+ ", password: " + listReult.get(i)[1]);
		}
		return listReult;
	}
}
